package com.bankLogisticsSys.service;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.bankLogisticsSys.util.ExcelExportUtil;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ManageMonitorService {
	
	public List<Record> getMonitorList(){
		String sqlEMS = "select id,network, "
				+ "("
				+ "select count(*) from expressinfo where source in "
				+ "(select networkid from expresspath where pathid = networkinfo.id) and sendDate = ?"
				+ ")"
				+ "pack, "
				+ "(select count(*) from expressinfo where source in "
				+ "(select networkid from expresspath where pathid = networkinfo.id) and sendDate = ? and state = 0)"
				+ "accept, "
				+ "(select count(*) from expressprocess where networkID = networkinfo.id and action = 2 and date(time) = ?)"
				+ "receive, "
				+ "(select count(*) from expressinfo where source in "
				+ "(select networkid from expresspath where pathid = networkinfo.id) and sendDate = ? and abnormal <> 0)"
				+ "abnormal "
				+ "from networkinfo "
				+ "where type in (3,4) order by type";
		
		java.util.Date date = new java.util.Date(System.currentTimeMillis());
		Date currentDate = new Date(date.getTime());
		List<Record> monitorList = Db.find(sqlEMS, currentDate, currentDate, currentDate, currentDate);
		for(Record x:monitorList){
			String abnormalMsg = "";
			List<Record> expressList = Db.find("select * from expressinfo where source in "
					+ "(select networkid from expresspath where pathid = ?) and sendDate = ? and abnormal <> 0", x.getInt("id"), currentDate);
			for(Record y:expressList){
				switch(y.getInt("abnormal")){
				case 0:break;
				case 2:abnormalMsg += "快递单 " + y.getLong("trackingID")+" 线路异常";break;
				case 1:abnormalMsg += "快递单 " + y.getLong("trackingID")+" 操作异常";break;
				case 3:abnormalMsg += "快递单 " + y.getLong("trackingID")+" 超时";break;
				case 4:abnormalMsg += "快递单 " + y.getLong("trackingID")+" 包裹数异常";break;
				}
			}
			x.set("explain", abnormalMsg);
		}
		return monitorList;
	}
	
	public List<Record> getMonitorList2(String networkinfo, Date searchDate){
		String condition="";
		if(StringUtils.isNotBlank(networkinfo)){
			condition += " and network like " + "'%"+networkinfo+"%'";
		}
		//sql string from manageSQL.sql
		String network = Db.getSql("monitor.network");
		String packPackage = Db.getSql("monitor.packPackage");
		String receivePackage = Db.getSql("monitor.receivePackage");
		String acceptPackage = Db.getSql("monitor.acceptPackage");
		String abnormalPackage = Db.getSql("monitor.abnormalPackage");
		System.out.println(network);
		System.out.println(packPackage);
		System.out.println(receivePackage);
		System.out.println(acceptPackage);
		System.out.println(abnormalPackage);
		List<Record> monitorList = Db.find(network+condition);
		System.out.println(network+ condition);
		for(Record x:monitorList){
			Date currentDate = searchDate;
			int networkid = x.getInt("id");
			long packNum = Db.queryLong(packPackage,networkid,currentDate);
			long receiveNum = Db.queryLong(receivePackage,networkid,currentDate);
			long acceptNum = Db.queryLong(acceptPackage,networkid,currentDate);
			long abnormalNum = packNum-receiveNum + Db.queryLong(abnormalPackage,networkid,currentDate);
			String explain = (packNum-receiveNum)+ "件缺少过站记录" + Db.queryLong(abnormalPackage,networkid,currentDate) + "件线路异常";
			x.set("pack", packNum);
			x.set("receive", receiveNum);
			x.set("accept", acceptNum);
			x.set("abnormal", abnormalNum);
			x.set("explain", explain);
		}
		return monitorList;
	}
}
